import psycopg2
from psycopg2 import sql

# 定义数据库连接参数
db_params = {
    'dbname': 'odp20210825',
    'user': 'postgres',
    'password': 'j3IA6rebQ9NG',
    'host': '192.168.6.226',
    'port': '5432'
}

# 连接数据库
conn = psycopg2.connect(**db_params)
cursor = conn.cursor()

try:
    # 查询获取所有去重且去除空格后的 department 值及对应的医院名和数量
    cursor.execute("""
        SELECT DISTINCT 医院名, TRIM(replace(department, ' ', '')) AS trimmed_department, 
               COUNT(*) AS count
        FROM temp_20240914_1
        GROUP BY 医院名, TRIM(replace(department, ' ', ''))
    """)
    results = cursor.fetchall()

    for hospital_row in results:
        hospital_name = hospital_row[0]
        trimmed_department = hospital_row[1]
        count = hospital_row[2]

        if count > 0:
            # 更新 temp_20240914 表中的对应列
            query = sql.SQL("""
                UPDATE temp_20240914 
                SET {field} = %s 
                WHERE 医院名 = %s AND 眼科医生数 <> ''
            """).format(
                field=sql.Identifier(trimmed_department)
            )

            cursor.execute(query, (count, hospital_name))

    # 提交事务
    conn.commit()

except Exception as e:
    print(f"Error: {e}")
    conn.rollback()

finally:
    # 关闭游标和连接
    cursor.close()
    conn.close()
